import pandas as pd
from db_conn import pd_conn_zfw


# 用于获取一个datetime当天的秒数转化的小时数
def get_hour(date_time):
    hour = date_time.hour
    minute = date_time.minute
    second = date_time.second
    return hour + (minute * 60 + second) / 60 / 60


def get_second(date_time):
    hour = date_time.hour
    minute = date_time.minute
    second = date_time.second
    return hour * 60 * 60 + (minute * 60 + second)


# 用于获取两个datetime的8-21时段营业时长h
def hour_diff(st, et, wt):
    max_hour = (et.day_of_year - st.day_of_year) * 13
    return min(max((et.day_of_year - wt.day_of_year) * 13 - min(max(get_hour(wt) - 8, 0), 13), 0.0000001), max_hour)


# 运营率汇总表
def oprate_rate_total(startTime, endTime):
    # 时间序列
    dates = pd.date_range(startTime, pd.to_datetime(endTime) - pd.Timedelta(days=1), freq='D').tolist()

    df_month = pd.DataFrame([], columns=['f_id', 'diff(h)'])

    for dt in dates:
        date = dt.strftime('%Y-%m-%d')

        sql = """
            select
                f_factory_id f_id
                , TIMESTAMPDIFF(second
                    ,STR_TO_DATE(if(
                        f_startTime<concat('{_date}',' 08:00:00')
                        , concat('{_date}',' 08:00:00') 
                        , f_startTime),'%Y-%m-%d %H:%i:%s')
                    ,STR_TO_DATE(if(
                        f_endTime>concat('{_date}',' 21:00:00')
                        , concat('{_date}',' 21:00:00')
                        , f_endTime),'%Y-%m-%d %H:%i:%s'))/60/60 'diff(h)'
            from t_factory_business fb
            join t_factory f on f.f_id = fb.f_factory_id and f.f_province_id=45 and f.f_business_category='中石化'
            where
                fb.f_startTime < concat('{_date}',' 21:00:00')
                and (
                        fb.f_endTime >= concat('{_date}',' 08:00:00')
                        or fb.f_endTime is null
                )
                and fb.f_work_status in (1, 8)
            order by f_id
        """.format(_date=date)

        df_day = pd_conn_zfw(sql)

        df_month = pd.concat([df_month, df_day]).sort_values('f_id')

    # 按站点汇总运维时长
    df_group = df_month.groupby('f_id').sum()

    # 获取站点信息
    sql = """
            select 
                f_id,f_name,f_province,f_city,f_work_start_time 
                , if(f_station_num in (1,2,3,4,5,6,6001,6002), '愿景'
                    , if(f_station_num = 10, '阿尔法'
                        , if(f_station_num in (6003, 6004), 'LITE'
                            , if(f_station_num = 8001, '隧道机', '其他')))) f_station_num
            from t_factory 
            where f_status = 0 and f_work_start_time < '{_endTime}'
            # and f_id = 9218
        """.format(_endTime=endTime)
    df_fact = pd_conn_zfw(sql)

    df_fact = df_fact.set_index('f_id')
    df_fact['f_work_start_time'] = pd.to_datetime(df_fact['f_work_start_time'])

    st = pd.to_datetime(startTime + ' 08:00:00')
    et = pd.to_datetime(endTime)

    # st = pd.to_datetime(startTime)
    # df_fact['total_time'] = df_fact['f_work_start_time'].apply(lambda x: hour_diff(st, et, x))

    # 开业时间在统计开始时间之前的，为整个运营天数*13个运营时长
    df_fact['total_time'] = 0.00000001
    df_fact.loc[
        (df_fact['f_work_start_time'] <= st), 'total_time'
    ] = (et - st).days * 13 + 13

    # 开业时间在统计开始时间之后的，除开业当天运营天数*13个运营时长
    df_fact['tail_time'] = 0
    df_fact.loc[
        (df_fact['f_work_start_time'] > st), 'tail_time'
    ] = (et - df_fact.loc[(df_fact['f_work_start_time'] > st), 'f_work_start_time']).apply(lambda x: x.days) * 13
    # print(df_fact.info())

    # 开业时间在统计开始时间之后的，当天运营时长
    df_fact['cut_time'] = 0
    df_fact.loc[
        (df_fact['f_work_start_time'] > st), 'cut_time'
    ] = (df_fact.loc[(df_fact['f_work_start_time'] > st), 'f_work_start_time']).apply(lambda x: 21 - get_second(x)/60/60)

    # 如果是20点之后开业的，当天运营时长=0
    df_fact.loc[
        (df_fact['cut_time'] < 0), 'cut_time'
    ] = 0

    # 开业时间在统计开始时间之后的，总运营时长=尾部时长+当天时长
    df_fact.loc[
        (df_fact['f_work_start_time'] > st), 'total_time'
    ] = df_fact.loc[(df_fact['f_work_start_time'] > st), 'tail_time'] + df_fact.loc[
        (df_fact['f_work_start_time'] > st), 'cut_time']

    # 运维时长与运营时长表结合
    df_m = df_group.merge(df_fact, how='right', on='f_id')

    # 运营率计算
    df_m.loc[df_m['total_time'] == 0, '运营率'] = 1
    df_m.loc[df_m['total_time'] != 0, '运营率'] = 1 - df_m.loc[df_m['total_time'] != 0, 'diff(h)'] / df_m.loc[df_m['total_time'] != 0, 'total_time']

    # 没有运维的站点，运营率为1
    df_m['运营率'] = df_m['运营率'].fillna(1)
    df_m['diff(h)'] = df_m['diff(h)'].fillna(0)

    # 运营率转为两位小数百分比数
    df_m['运营率'] = df_m['运营率'].apply(lambda x: round(x * 100, 2))

    # 运营率汇总表完毕，待导出
    df_m.columns = ['运维时长(h)', '站点名称', '省', '市', '开业时间', '机型', '应运营总时长', 'tail_time', 'cut_time', '运营率%']
    df_m = df_m[['站点名称', '省', '市', '开业时间', '机型', '应运营总时长', '运维时长(h)', '运营率%']]
    df_m = df_m.reset_index()

    return df_m.to_csv(r'运营率{}~{}.csv'.format(startTime, endTime), index=False)


if __name__ == '__main__':
    startTime = '2022-02-23'
    endTime = '2022-03-23'
    oprate_rate_total(startTime, endTime)

